/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.model;

/**
 * @author Administrador
 */
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.db.SqlManager;
import com.exception.DataException;
import com.javabeans.DiaSemana;
import com.javabeans.PeriodoDia;

public class AdminPeriodoDia implements PeriodoDiaManager{
    private Connection c;

    public AdminPeriodoDia(Connection c){
        this.c = c;
    }
    
    public int getIdPeriodoDia(int idFacultad) throws DataException {
        int res = 0;
        ResultSet rs = null;
        PreparedStatement preSt = null;
        String query = "SELECT IFNULL(MAX(IDPERIODO_DIA)+1,1) AS ID FROM PERIODO_DIA WHERE IDFACULTAD="+idFacultad;
        try{
            preSt = c.prepareStatement(query);
            rs = preSt.executeQuery();
            rs.next();
            res = rs.getInt("ID");
        }catch(SQLException e){
            throw new DataException(" AdminPeriodoDia -> getIdPeriodoDia() "+e.getMessage());
        }finally{
            SqlManager.closeRs(rs);
            SqlManager.closePreSt(preSt);
        }
        return res;
    }
    
    public void savePeriodoDia(PeriodoDia pd) throws DataException{
        PreparedStatement preSt = null;
        try{
            String query = "INSERT INTO PERIODO_DIA VALUES";
            query += "("+pd.getIdFacultad()+","+pd.getIdPeriodoDia();
            query += ",'"+pd.getHoraIni();
            query += "','"+pd.getHoraFin();
            query += "',"+pd.isEstraordinario()+")";            
            preSt = c.prepareStatement(query);
            preSt.executeUpdate();
        }catch(SQLException e){
            throw new DataException(" AdminPeriodoDia -> savePeriodoDia(PeriodoDia pd) SQLException = "+e.getMessage());
        }finally{
            SqlManager.closePreSt(preSt);
        }
    }
    
    @Override
    public List<PeriodoDia> getPeriodosDia(int idFacultad) throws DataException{
        List<PeriodoDia> periodos = new ArrayList<PeriodoDia>();
        ResultSet rs = null;
        PreparedStatement preSt = null;
        try{
            String query = "SELECT IDPERIODO_DIA";
            query += ", DATE_FORMAT(HORAINI,'%H:%i') HORAINI";
            query += ", DATE_FORMAT(HORAFIN,'%H:%i') HORAFIN";
            query += ", EXTRAORDINARIO";
            query += " FROM PERIODO_DIA";
            query += " WHERE IDFACULTAD="+idFacultad;
            query += " ORDER BY HORAINI";            
            preSt = c.prepareStatement(query);
            rs = preSt.executeQuery();
            while(rs.next()){
                PeriodoDia pd = new PeriodoDia();
                pd.setIdFacultad(idFacultad);
                pd.setIdPeriodoDia(rs.getInt("IDPERIODO_DIA"));
                pd.setHoraIni(rs.getString("HORAINI"));
                pd.setHoraFin(rs.getString("HORAFIN"));
                pd.setEstraordinario(rs.getBoolean("EXTRAORDINARIO"));
                periodos.add(pd);
            }
        }catch(SQLException sql){
            throw new DataException(" AdminPeriodoDia -> getPeriodosDia(int idFacultad) e = "+sql.getMessage());
        }finally{
            SqlManager.closeRs(rs);
            SqlManager.closePreSt(preSt);
        }
        return periodos;
    }
    
    public PeriodoDia getPeriodoDia(int idFacultad,int idPeriodoDia) throws DataException{
        PeriodoDia pd = new PeriodoDia();
        ResultSet rs = null;
        PreparedStatement preSt = null;
        try{
            String query = "SELECT IDPERIODO_DIA";
            query += ", DATE_FORMAT(HORAINI,'%H:%i') HORAINI";
            query += ", DATE_FORMAT(HORAFIN,'%H:%i') HORAFIN";
            query += ", EXTRAORDINARIO";
            query += " FROM PERIODO_DIA";
            query += " WHERE IDFACULTAD="+idFacultad+" AND IDPERIODO_DIA="+idPeriodoDia;            
            preSt = c.prepareStatement(query);
            rs = preSt.executeQuery();
            if(rs.next()){
                pd.setIdFacultad(idFacultad);
                pd.setIdPeriodoDia(rs.getInt("IDPERIODO_DIA"));
                pd.setHoraIni(rs.getString("HORAINI"));
                pd.setHoraFin(rs.getString("HORAFIN"));
                pd.setEstraordinario(rs.getBoolean("EXTRAORDINARIO"));
            }
        }catch(SQLException sql){
            throw new DataException(" AdminPeriodoDia -> getPeriodoDia(int idFacultad,int idPeriodoDia) e = "+sql.getMessage());
        }finally{
            SqlManager.closeRs(rs);
            SqlManager.closePreSt(preSt);
        }
        return pd;
    }
    
    @Override
    public PeriodoDia getPeriodoDiaAll(int idFacultad,int idPeriodoDia) throws DataException{
        PeriodoDia pd = new PeriodoDia();
        List<DiaSemana> diasSemana = new ArrayList<DiaSemana>();
        ResultSet rs = null;
        PreparedStatement preSt = null;
        try{
            int i = 0;
            String query = "SELECT P.IDPERIODO_DIA IDPERIODO_DIA";
            query += ", DATE_FORMAT(P.HORAINI,'%H:%i') HORAINI";
            query += ", DATE_FORMAT(P.HORAFIN,'%H:%i') HORAFIN";
            query += ", D.IDDIA_SEMANA IDDIASEMANA";
            query += ", D.NOMBRE";
            query += " FROM PERIODO_DIA P";
            query += " INNER JOIN (HORARIO H INNER JOIN DIA_SEMANA D ON H.IDDIA_SEMANA=D.IDDIA_SEMANA)";
            query += " ON P.IDFACULTAD=H.IDFACULTAD AND P.IDPERIODO_DIA=H.IDPERIODO_DIA AND H.ACTIVO=1";
            query += " WHERE P.IDFACULTAD="+idFacultad+" P.IDPERIODO_DIA="+idPeriodoDia;            
            preSt = c.prepareStatement(query);
            rs = preSt.executeQuery();
            while(rs.next()){
                if(i == 0){
                    pd.setIdPeriodoDia(rs.getInt("IDPERIODO_DIA"));
                    pd.setHoraIni(rs.getString("HORAINI"));
                    pd.setHoraFin(rs.getString("HORAFIN"));
                }
                DiaSemana ds = new DiaSemana();
                ds.setIdDiaSemana(rs.getInt("IDDIASEMANA"));
                ds.setNombre(rs.getString("NOMBRE"));
                diasSemana.add(ds);
                i++;
            }
            pd.setDiasSemana(diasSemana);
        }catch(SQLException sql){
            throw new DataException(" AdminPeriodoDia -> getPeriodoDiaAll(int idFacultad,int idPeriodoDia) e = "+sql.getMessage());
        }finally{
            SqlManager.closeRs(rs);
            SqlManager.closePreSt(preSt);
        }
        return pd;
    }
    
    @Override
    public void editPeriodoDia(PeriodoDia pd) throws DataException {
        PreparedStatement preSt = null;
        try{
            String query = "UPDATE PERIODO_DIA SET HORAINI = '"+pd.getHoraIni();
            query += "', HORAFIN = '"+pd.getHoraFin();
            query += "' WHERE IDFACULTAD="+pd.getIdFacultad()+" AND IDPERIODO_DIA = "+pd.getIdPeriodoDia();            
            preSt = c.prepareStatement(query);
            preSt.executeUpdate();
        }catch(SQLException e){
            throw new DataException(" AdminPeriodoDia -> editPeriodoDia(PeriodoDia pd) DataException = "+e.getMessage());
        }finally{
            SqlManager.closePreSt(preSt);
        }
    }
}
